from DataAccess.DBConnFactory import *

from Misc.Utils import *
from string import Template

def query_financial_ratio(ref_date, tick_list = None, exch_list = ['CNSESH', 'CNSESZ']):
	
	sql_tpl = Template('''select sc.SYMBOL, fr.bps, fr.eps
						  from (select COMPANYCODE,
										MFRatio18 as bps,
										MFRatio14 as eps,
										rank() over(partition by COMPANYCODE order by ReportDate desc) as rk
								  from MFRatio
								 where ReportDate <= TO_DATE('${DATE}', 'yyyy-mm-dd')) fr,
							   SECURITYCODE sc
							${TICK_CRITERIA}
							and fr.COMPANYCODE = sc.COMPANYCODE
							and ${EXCH_CRITERIA}
							and fr.rk = 1''')
	sql_text = sql_tpl.substitute(DATE = ref_date.isoformat(),
									EXCH_CRITERIA = format_sql_criteria('sc.EXCHANGE', exch_list),
									TICK_CRITERIA = format_sql_tick_criteria('sc.SYMBOL', tick_list))

	cursor = DBConnFactory().get_db_connection('FINCHINA').cursor()
	cursor.execute(sql_text)
	r = cursor.fetchall()
	
	fr = {}	
	for elem in r:
		fr[elem[0]] = (elem[1], elem[2])
			
	return fr